{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 集思录转债\n",
    "\n",
    "- 作者：DGC'Idea\n",
    "- 版本：V0.1   \n",
    "- 更新日期：2018年3月20日\n",
    "\n",
    "----------\n",
    "\n",
    "- 研究目的：获取可转债的相关信息；\n",
    "- 研究方法：从集思录网站爬取可转债相关数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:205: SettingWithCopyWarning: \nA value is trying to be set on a copy of a slice from a DataFrame\n\nSee the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n  self._setitem_with_indexer(indexer, value)\n 转债数据获取失败！\n"
    }
   ],
   "source": [
    "#导入库\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import json\n",
    "import os\n",
    "\n",
    "#引入自定义库\n",
    "import sys\n",
    "sys.path.append('../DS')\n",
    "from ds_jisilu import *\n",
    "\n",
    "#设置pd显示行列数目\n",
    "pd.set_option('display.max_rows',500)\n",
    "pd.set_option('display.max_columns',100)\n",
    "pd.set_option('display.width',10000)\n",
    "\n",
    "#已上市可转债\n",
    "def get_cb(data_type):\n",
    "    df=get_jsl_data(data_type)\n",
    "    #过滤掉可交换债\n",
    "    df=df[df['btype']=='C']\n",
    "    #更改列标题\n",
    "    df=df.rename(columns={\n",
    "        'bond_id':'代码','bond_nm':'转债名称','price':'现价','increase_rt':'涨跌',\n",
    "        'stock_id':'正股代码','stock_nm':'正股名称','sprice':'股价','sincrease_rt':'涨跌 ',\n",
    "        'convert_dt':'转股起始日','convert_price':'转股价','convert_value':'转股价值',\n",
    "        'premium_rt':'溢价率','convert_amt_ratio':'转债比',\n",
    "        'force_redeem_price':'强赎触发价','redeem_price_ratio':'强赎触发比',\n",
    "        'put_price':'回售价',   \n",
    "        'next_put_dt':'回售起始日','put_convert_price':'回售触发价','put_convert_price_ratio':'回售触发比',             \n",
    "        'short_maturity_dt':'到期时间','year_left':'剩余(年)','redeem_price':'到期赎回价',    \n",
    "        'ytm_rt':'税前收益','ytm_rt_tax':'税后收益',           \n",
    "        'orig_iss_amt':'规模(亿)','curr_iss_amt':'剩余规模(亿)','volume':'成交额(万)','repo_discount_rt':'折算率',           \n",
    "        'put_tc':'回售条款', 'redeem_tc':'强赎条款','cpn_desc':'利息','adjust_tc':'向下修正',                        \n",
    "         }) \n",
    "    return df\n",
    "\n",
    "#待发、待上市可转债\n",
    "def get_cbnew(data_type):\n",
    "    df=get_jsl_data(data_type)\n",
    "    #过滤掉可交换债\n",
    "    df=df[df['cb_type']=='可转债']\n",
    "    #更改列标题\n",
    "    df=df.rename(columns={\n",
    "        'bond_id':'代码','bond_nm':'转债名称',\n",
    "        'stock_id':'正股代码','stock_nm':'正股名称',\n",
    "        'amount':'发行规模(亿)',\n",
    "        'progress_nm':'进展','progress_dt':'进展日期',\n",
    "        'cb_amount':'百元股票含权(元)',\n",
    "        'convert_price':'转股价','price':'现价','increase_rt':'涨幅','pma_rt':'现价比转股价',\n",
    "        'ration_rt':'股东配售率','online_amount':'网上规模(亿)',\n",
    "        'lucky_draw_rt':'中签率','single_draw':'单账户中签(顶格)','valid_apply':'申购户数(万)',   \n",
    "         }) \n",
    "    #整理进展信息   \n",
    "    for i in range(len(df)):\n",
    "        #判断是否是已发\n",
    "        if df['cp_flag'].iloc[i]=='Y':\n",
    "            #清除多余字符\n",
    "            progress=df['进展'].iloc[i].replace('<br>','')\n",
    "            df['进展日期'].iloc[i]=progress[0:10]\n",
    "            if progress[10:12]=='上市':\n",
    "                df['进展'].iloc[i]=progress[10:12]\n",
    "            else:\n",
    "                df['进展'].iloc[i]=progress[10:12]+'（'+progress[14:]+'）'\n",
    "    return df\n",
    "\n",
    "try:\n",
    "    cbnew_df=get_cbnew('new_cbond')\n",
    "    cb_df=get_cb('sell_cbond')\n",
    "    print(' 转债数据已准备好！')\n",
    "except Exception as e:\n",
    "    print(' 转债数据获取失败！')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.已上市转债"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.基本信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'cb_df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-11-782ad06e7a08>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m#基本信息\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m df=cb_df.loc[:,(\n\u001b[0m\u001b[0;32m      3\u001b[0m     \u001b[1;34m'代码'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'转债名称'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'现价'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'涨跌'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m     \u001b[1;34m'正股名称'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'股价'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'涨跌 '\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m     \u001b[1;34m'转股价'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'转股价值'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'溢价率'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'cb_df' is not defined"
     ]
    }
   ],
   "source": [
    "#基本信息\n",
    "df=cb_df.loc[:,(\n",
    "    '代码','转债名称','现价','涨跌',\n",
    "    '正股名称','股价','涨跌 ',    \n",
    "    '转股价','转股价值','溢价率',\n",
    "    '到期时间','剩余(年)',        \n",
    "    '税前收益','税后收益',           \n",
    "    '规模(亿)','成交额(万)',\n",
    "    )]\n",
    "#按照现价筛选（现价小于100）并排序\n",
    "df['现价']=df['现价'].astype('float')\n",
    "df=df[df['现价']<100]\n",
    "df=df.sort_values('现价',ascending=True)\n",
    "#重置索引\n",
    "df=df.reset_index()\n",
    "del df['index']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2.回售、强赎"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "#回售、强赎信息\n",
    "df=cb_df.loc[:,(\n",
    "    '代码','转债名称','现价','转股价',\n",
    "    '转股起始日','强赎触发价','强赎触发比',        \n",
    "    '回售起始日','回售触发价','回售触发比','回售价','到期赎回价',\n",
    "    '转债比','折算率',       \n",
    "    )]\n",
    "#按照现价筛选（现价小于100）并排序\n",
    "df['现价']=df['现价'].astype('float')\n",
    "df=df[df['现价']<100]\n",
    "df=df.sort_values('现价',ascending=True)\n",
    "#重置索引\n",
    "df=df.reset_index()\n",
    "del df['index']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3.条款信息"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.3.1.单条查看"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "#设置要查看的转债代码\n",
    "cb_id='128016'\n",
    "df=cb_df[cb_df['代码']==cb_id]\n",
    "#重置索引\n",
    "pd.set_option('max_colwidth', 250)\n",
    "df.index=df['代码']+' '+df['转债名称']\n",
    "df.index.name=None\n",
    "df=df.loc[:,(\n",
    "    '回售条款', '强赎条款','利息','向下修正',    \n",
    "    )]\n",
    "df.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.3.2.查看所有"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('max_colwidth', 150)\n",
    "cb_df.index=cb_df['代码']+cb_df['转债名称']\n",
    "df.index.name=None\n",
    "df=cb_df.loc[:,(\n",
    "    '回售条款', '强赎条款','向下修正','利息',  \n",
    "    )]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.待发、待上市转债"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1.待申购"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "#筛选待申购    \n",
    "df=cbnew_df[cbnew_df['ap_flag']=='B']\n",
    "df=df.loc[:,(\n",
    "    '正股代码','正股名称',\n",
    "    '发行规模(亿)','进展','进展日期',\n",
    "    '百元股票含权(元)','转股价','现价','涨幅','现价比转股价',    \n",
    "    )] \n",
    "#更改列名\n",
    "df=df.rename(columns={'进展日期':'申购日期','进展':'申购代码'}) \n",
    "df['申购代码']=df['申购代码'].str[5:11]\n",
    "#重置索引\n",
    "df=df.reset_index()\n",
    "del df['index']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2.待上市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "#筛选待上市   \n",
    "df=cbnew_df[cbnew_df['ap_flag']=='E']\n",
    "df=df.loc[:,(\n",
    "    '代码','转债名称',\n",
    "    '正股名称',\n",
    "    '进展日期',\n",
    "    '转股价','现价','涨幅',\n",
    "    '股东配售率','网上规模(亿)',\n",
    "    '中签率','单账户中签(顶格)','申购户数(万)', \n",
    "    '发行规模(亿)',\n",
    "    )]\n",
    "df=df.rename(columns={'进展日期':'上市日期',}) \n",
    "#重置索引\n",
    "df=df.reset_index()\n",
    "del df['index']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.已申购"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "#筛选待申购    \n",
    "df=cbnew_df[cbnew_df['ap_flag']=='C']\n",
    "df=df.loc[:,(\n",
    "    '代码','转债名称',\n",
    "    '正股名称',\n",
    "    '进展日期',\n",
    "    '转股价','现价','涨幅',\n",
    "    '股东配售率','网上规模(亿)',\n",
    "    '中签率','单账户中签(顶格)','申购户数(万)', \n",
    "    '发行规模(亿)',\n",
    "    )]\n",
    "df=df.rename(columns={'进展日期':'申购日期',}) \n",
    "#重置索引\n",
    "df=df.reset_index()\n",
    "del df['index']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4.待发售"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "#所有待发售\n",
    "df=cbnew_df[cbnew_df['ap_flag']=='N']\n",
    "df=df.loc[:,(\n",
    "    '正股代码','正股名称',\n",
    "    '发行规模(亿)','进展','进展日期',\n",
    "    '百元股票含权(元)','转股价','现价','涨幅','现价比转股价',    \n",
    "    )] \n",
    "#重置索引\n",
    "df=df.reset_index()\n",
    "del df['index']\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.4 64-bit ('base': conda)",
   "language": "python",
   "name": "python37464bitbasecondad5f255661c6c454290a08f256a06a34d"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}